package com.zcyr.core.impl;

import com.alibaba.druid.util.JdbcUtils;
import com.zcyr.core.AbstractDatabaseEnvironment;
import com.zcyr.core.AbstractDatabaseEnvironmentManager;
import com.zcyr.exception.DatabaseEnvironmentException;
import com.zcyr.exception.DatabaseEnvironmentManagerException;
import com.zcyr.mapper.DatabaseEnvironmentInfoMapper;
import com.zcyr.pojo.DatabaseEnvironmentInfo;
import com.zcyr.util.DatabaseTool;
import com.zcyr.util.ResultSetTool;
import com.zcyr.util.SQLTrim;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentLinkedQueue;

@Component
@ConditionalOnProperty(value = "tryme.databaseEnvironmentManager.enable_pool", havingValue = "false", matchIfMissing = true)
public class DatabaseEnvironmentManager extends AbstractDatabaseEnvironmentManager{
    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private DataSource dataSource;

    @Autowired
    private DatabaseTool databaseTool;

    @Autowired
    private DatabaseEnvironmentInfoMapper mapper;

    @Value("${tryme.databaseEnvironmentManager.grantRole:NULL}")
    private String grantRole;

    @Value("${spring.datasource.druid.url}")
    private String databaseUrl;

    @Value("${tryme.databaseEnvironmentManager.maxEnvironmentNum:32}")
    private int maxEnvironmentNum;

    @Value("${tryme.databaseEnvironmentManager.releaseDatabaseWhenStartUp:false}")
    private boolean releaseDatabaseWhenStartUp;

    @Value("${tryme.databaseEnvironmentManager.database}")
    private String database;
    private final Map<String, AbstractDatabaseEnvironment> databaseEnvironmentMap = new ConcurrentHashMap<>();
    private volatile boolean isCleaningUnReleaseQue = false;
    private final Queue<AbstractDatabaseEnvironment> unReleasedEnvironmentQue = new ConcurrentLinkedQueue<>();
    protected final Queue<AbstractDatabaseEnvironment> readiedDatabaseEnvironmentQue = new ConcurrentLinkedQueue<>();

    @Override
    public void afterPropertiesSet() throws DatabaseEnvironmentException {
        this.init();
        if (releaseDatabaseWhenStartUp) {
            //TODO 数据库环境管理初始化
            logger.warn("TODO: release users when start up");
        }
    }

    @Override
    protected void init() throws DatabaseEnvironmentException {
        try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(
                    "CREATE TABLE if not exists public.database_environment_info (\n" +
                            "id varchar NOT NULL,\n" +
                            "create_time timestamp NOT NULL,\n" +
                            "expire_seconds int4 NOT NULL DEFAULT 0,\n" +
                            "active_time timestamp NOT NULL,\n" +
                            "reset_times int4 NOT NULL,\n" +
                            "username varchar NOT NULL,\n" +
                            "\"password\" varchar NOT NULL,\n" +
                            "suffix varchar NULL,\n" +
                            "CONSTRAINT database_environment_info_pk PRIMARY KEY (id)\n" +
                            ")\n" +
                            "WITH (\n" +
                            "orientation=row,\n" +
                            "compression=no\n" +
                            ");\n"
            );
            stmt.executeQuery("select * from public.database_environment_info");
            ArrayList<HashMap<String, String>> results = ResultSetTool.getAllData(stmt.getResultSet());
            logger.info(String.format("当前数据库共有%d个数据库用户", results.size()));
            logger.error("initialize databaseEnvironmentManager success!");
            //TODO 回收已经记录在info表的过期数据库环境, 并使用.
        } catch (SQLException e) {
            logger.error("initialize databaseEnvironmentManager error!");
            throw new DatabaseEnvironmentException(e);
        }
    }

    @Override
    public AbstractDatabaseEnvironment getEnvironment(String uuid) {
        if (uuid == null) {
            return null;
        }
        return databaseEnvironmentMap.getOrDefault(uuid, null);
    }

    private DatabaseEnvironmentInfo createEnvironmentInfo(){
        DatabaseEnvironmentInfo info = new DatabaseEnvironmentInfo();
        info.setId(generateUUID());
        info.setUsername("tryme_user" + info.getId());
        info.setSuffix(SQLTrim.generateSuffix(info.getId()));
        info.setPassword("tryme@8848");
        info.setExpire_seconds(1800);
        return info;
    }

    public int getEnvironmentMapSize(){
        return databaseEnvironmentMap.size();
    }

    @Override
    public synchronized AbstractDatabaseEnvironment getAvailableEnvironment() throws DatabaseEnvironmentManagerException {
        if (this.readiedDatabaseEnvironmentQue.isEmpty()) {
            if (getEnvironmentMapSize() >= this.maxEnvironmentNum) {
                logger.warn("已经达到最大可创建环境数量max: " + this.maxEnvironmentNum);
                throw new DatabaseEnvironmentManagerException("已经达到最大可创建环境数量max: " + this.maxEnvironmentNum);
            }
            return this.newDatabaseEnvironment(createEnvironmentInfo());
        }
        return this.readiedDatabaseEnvironmentQue.poll();
    }

    @Override
    public void releaseDatabaseEnvironment(String uuid) {
        AbstractDatabaseEnvironment environment = getEnvironment(uuid);

        int releaseMemberCount = 0;
        int releaseTableCount = 0;
        int releaseSchemaCount = 0;
        try {
            releaseMemberCount = environment.releaseUsers();
            releaseTableCount = environment.releaseTables();
            releaseSchemaCount = environment.releaseSchemas();
        } catch (DatabaseEnvironmentException e) {
            logger.error("无法释放数据库环境资源, 环境: " + environment + "DatabaseEnvironmentException: " + e.getMessage());
            this.unReleasedEnvironmentQue.add(environment);
        }
        logger.info(String.format(
                "\n释放用户(%s)资源, 成功释放了:" +
                        " %s个子用户" +
                        " %s个模式对象" +
                        " %s个表对象",
                environment.getUsername(),
                releaseMemberCount,
                releaseSchemaCount,
                releaseTableCount));
        this.readiedDatabaseEnvironmentQue.add(environment);
        // 更新info表
        environment.getInfo().increaseResetTimes();
        mapper.updateById(environment.getInfo());
    }

    private void empower(Statement stmt, AbstractDatabaseEnvironment environment, String grantRole) throws SQLException {
        String username = environment.getUsername();
        stmt.execute(String.format("GRANT CREATE ON DATABASE %s TO %s", database, username));
        if (grantRole != null) {
            stmt.execute(String.format("GRANT %s TO %s", grantRole, username));
        }
    }

    @Override
    public AbstractDatabaseEnvironment newDatabaseEnvironment(DatabaseEnvironmentInfo info) throws DatabaseEnvironmentManagerException {
        Statement stmt = null;
        try (Connection conn = dataSource.getConnection()) {
            try{
                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                AbstractDatabaseEnvironment environment = new DatabaseEnvironment(databaseUrl, info);
                String sql = String.format(
                        "CREATE USER %s WITH CREATEROLE PASSWORD '%s'",
                        environment.getUsername(),
                        environment.getPassword()
                );
                stmt.execute(sql);
                environment.setInfo(info);
                empower(stmt, environment, grantRole.equals("NULL") ? null : grantRole);
                this.registerEnvironment(environment);
                conn.commit();
                return environment;
            } catch (SQLException e){
                conn.rollback();
                throw e;
            }
        } catch (Exception e) {
            throw new DatabaseEnvironmentManagerException("无法创建环境", e);
        } finally {
            JdbcUtils.close(stmt);
        }
    }

    protected void registerEnvironment(AbstractDatabaseEnvironment environment) {
        mapper.insert(environment.getInfo());
        databaseEnvironmentMap.put(environment.getUuid(), environment);
    }

    private synchronized void checkAndClearUnReleasedEnvironment() {
        logger.info("检查是否需要清理未释放资源的环境...");
        if (!isCleaningUnReleaseQue && !unReleasedEnvironmentQue.isEmpty()) {
            isCleaningUnReleaseQue = true;
            Thread t = new Thread(() -> {
                int count = unReleasedEnvironmentQue.size();
                for (int i = 0; i < count; i++) {
                    if (!unReleasedEnvironmentQue.isEmpty()) {
                        releaseDatabaseEnvironment(unReleasedEnvironmentQue.poll().getUuid());
                    }
                }
                isCleaningUnReleaseQue = false;
            });
            logger.info("开始清理未释放资源的环境, 清理线程: " + t.getName() + ", 环境个数: " + unReleasedEnvironmentQue.size());
            t.start();
        } else {
            if (isCleaningUnReleaseQue) {
                logger.info("正在清理未释放资源的环境, 无需新建清理线程.");
            }
        }
    }

    /**
     * 为环境续期
     *
     * @param uuid 环境uuid
     */
    @Override
    public void renewal(String uuid) {
        DatabaseEnvironmentInfo info = getEnvironment(uuid).getInfo();
        info.setActive_time(new Date());
        mapper.updateById(info);
    }

    @Override
    public String status() {
        return String.format("当前环境数量: %d\n"
                        + "当前环境info数量%d\n",
                databaseEnvironmentMap.size(),
                getEnvironmentInfoMapSize());
    }
}
